﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Text.RegularExpressions;

namespace AliCMS.UserModule.OleDb
{
    public partial class User : OleDbBase, Dal.IUser
    {
        public int GetInnerIDFromGuid(Guid guid)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.User.ID, MapType(Alias.User.ID)).Value = guid;
            Command.CommandText = "SELECT " + MapColumn(Alias.User.InnerID) + " FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.ID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return 0;
            else
                return (int)result;
        }
        public Guid GetGuidFromInnerID(object id)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.User.InnerID, MapType(Alias.User.InnerID)).Value = id;
            Command.CommandText = "SELECT " + MapColumn(Alias.User.ID) + " FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.InnerID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return Guid.Empty;
            else
                return (Guid)result;
        }
        public Dictionary<string, object> Get(object id)
        {
            Dictionary<string, object> result = new Dictionary<string, object>();

            StringBuilder sql = new StringBuilder("SELECT TOP 1");
            sql.Append(" " + MapColumn(Alias.User.ID));
            sql.Append("," + MapColumn(Alias.User.InnerID));
            sql.Append("," + MapColumn(Alias.User.Name));
            sql.Append("," + MapColumn(Alias.User.Password));
            sql.Append("," + MapColumn(Alias.User.Mobile));
            sql.Append("," + MapColumn(Alias.User.IsMobileValidated));
            sql.Append("," + MapColumn(Alias.User.Nickname));
            sql.Append("," + MapColumn(Alias.User.RealName, Lang));
			sql.Append("," + MapColumn(Alias.User.Sex));
            sql.Append("," + MapColumn(Alias.User.Head));
            sql.Append("," + MapColumn(Alias.User.WxppOpenID));
			sql.Append("," + MapColumn(Alias.User.WxmpOpenID));
			sql.Append("," + MapColumn(Alias.User.WxUnionID));
			sql.Append("," + MapColumn(Alias.User.Profile, Lang));
            sql.Append("," + MapColumn(Alias.User.CreatorID));
            sql.Append("," + MapColumn(Alias.User.CreatorName));
            sql.Append("," + MapColumn(Alias.User.CreateTime));
            sql.Append("," + MapColumn(Alias.User.LastLoginTime));
            sql.Append("," + MapColumn(Alias.User.LastLoginIP));
            sql.Append("," + MapColumn(Alias.User.LoginTimes));
            sql.Append("," + MapColumn(Alias.User.IsChecked));
            sql.Append("," + MapColumn(Alias.User.IsLocked));
            sql.Append("," + MapColumn(Alias.User.IsDeleted));
            sql.Append(" FROM " + MapTable(Alias.Objects.User));

            if (id is Guid)
            {
                sql.Append(" WHERE " + MapColumn(Alias.User.ID) + "=?");

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.User.ID, MapType(Alias.User.ID)).Value = id;
            }
            else
            {
                sql.Append(" WHERE " + MapColumn(Alias.User.InnerID) + "=?");

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.User.InnerID, MapType(Alias.User.InnerID)).Value = id;
            }

            #region 权限
			/*
            if (CheckRight)
            {				
				Dictionary<string, string> user = Manager.User;
                #region 代理
                if (user["Namespace"] == "AliCMS.UserModule.Agent")
                {
                    sql.Append(" AND " + MapColumn(Alias.User.AgentID) + "=?");
                    Command.Parameters.Add("@" + Alias.User.AgentID, MapType(Alias.User.AgentID)).Value = new Guid(user["ID"]);
                }
                #endregion
                #region HasSelfLimitation
                else if (Convert.ToBoolean(user["HasSelfLimitation"]))
                {
                    sql.Append(" AND (" + MapColumn(Alias.User.ID) + "=?");
                    sql.Append(" OR " + MapColumn(Alias.User.CreatorID) + "=?)");
                    Command.Parameters.Add("@" + Alias.User.ID, MapType(Alias.User.ID)).Value = new Guid(user["ID"]);
                    Command.Parameters.Add("@" + Alias.User.CreatorID, MapType(Alias.User.CreatorID)).Value = new Guid(user["ID"]);
                }
                #endregion
            }
			*/
            #endregion
			
			Command.CommandText = sql.ToString();
            DataReader = Command.ExecuteReader();
            if (!DataReader.HasRows)
            {
                DataReader.Close();
                return null;
            }

            DataReader.Read();
            int i = 0;
            result[Alias.User.ID] = DataReader[i++];
            result[Alias.User.InnerID] = DataReader[i++];
            result[Alias.User.Name] = DataReader[i++];
            result[Alias.User.Password] = DataReader[i++];
            result[Alias.User.Mobile] = DataReader[i++];
            result[Alias.User.IsMobileValidated] = DataReader[i++];
            result[Alias.User.Nickname] = DataReader[i++];
            result[Alias.User.RealName] = DataReader[i++];
			result[Alias.User.Sex] = DataReader[i++];
            result[Alias.User.Head] = DataReader[i++];
            result[Alias.User.WxppOpenID] = DataReader[i++];
			result[Alias.User.WxmpOpenID] = DataReader[i++];
			result[Alias.User.WxUnionID] = DataReader[i++];
			result[Alias.User.Profile] = DataReader[i++];
            result[Alias.User.CreatorID] = DataReader[i++];
            result[Alias.User.CreatorName] = DataReader[i++];
            result[Alias.User.CreateTime] = DataReader[i++];
            result[Alias.User.LastLoginTime] = DataReader[i++];
            result[Alias.User.LastLoginIP] = DataReader[i++];
            result[Alias.User.LoginTimes] = DataReader[i++];
            result[Alias.User.IsChecked] = DataReader[i++];
            result[Alias.User.IsLocked] = DataReader[i++];
            result[Alias.User.IsDeleted] = DataReader[i++];
            DataReader.Close();

            return result;
        }
        public void Add(Dictionary<string, object> item)
        {
            Command.Parameters.Clear();

            #region 设置RowLang
            item.Add(Alias.User.RowLang, "|" + Lang + "|");
            #endregion

            StringBuilder names = new StringBuilder();
            StringBuilder values = new StringBuilder();

            PrepareParameters(names, values, item);

            Command.CommandText = "INSERT INTO " + MapTable(Alias.Objects.User) + "(" + names + ") VALUES(" + values + ")";
            Command.ExecuteNonQuery();
        }
        public void Modify(int id, Dictionary<string, object> changes)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.User.InnerID, MapType(Alias.User.InnerID)).Value = id;

            #region 检查并设置RowLang
            Command.CommandText = "SELECT " + MapColumn(Alias.User.RowLang) + " FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.InnerID) + "=?";
            string rowLang = Command.ExecuteScalar().ToString();
            if (!rowLang.Contains("|" + Lang + "|"))
                changes.Add(Alias.User.RowLang, rowLang + Lang + "|");
            #endregion

            StringBuilder sql = new StringBuilder("UPDATE " + MapTable(Alias.Objects.User) + " SET ");
            Command.Parameters.Clear();
            PrepareParameters(sql, changes);
            Command.Parameters.Add("@" + Alias.User.InnerID, MapType(Alias.User.InnerID)).Value = id;

            sql.Append(" WHERE " + MapColumn(Alias.User.InnerID) + "=?");

            #region 权限
			/*
            if (CheckRight)
            {
                Dictionary<string, string> user = Manager.User;
                #region 代理
                if (user["Namespace"] == "AliCMS.UserModule.Agent")
                {
                    sql.Append(" AND " + MapColumn(Alias.User.AgentID) + "=?");
                    Command.Parameters.Add("@" + Alias.User.AgentID, MapType(Alias.User.AgentID)).Value = new Guid(user["ID"]);
                }
                #endregion
                #region HasSelfLimitation
                else if (Convert.ToBoolean(user["HasSelfLimitation"]))
                {
                    sql.Append(" AND (" + MapColumn(Alias.User.ID) + "=?");
                    sql.Append(" OR " + MapColumn(Alias.User.CreatorID) + "=?)");
                    Command.Parameters.Add("@" + Alias.User.ID, MapType(Alias.User.ID)).Value = new Guid(user["ID"]);
                    Command.Parameters.Add("@" + Alias.User.CreatorID, MapType(Alias.User.CreatorID)).Value = new Guid(user["ID"]);
                }
                #endregion
            }
			*/
            #endregion

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public void Delete(int id)
        {
            #region 删除AppUser中的子用户
            //TODO
            #endregion

            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.User.InnerID, MapType(Alias.User.InnerID)).Value = id;

            StringBuilder sql = new StringBuilder("DELETE FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.InnerID) + "=?");

            #region 代理和HasSelfLimitation
			/*
            if (CheckRight)
            {
                Dictionary<string, string> user = Manager.User;
                if (user["Namespace"] == "AliCMS.UserModule.Agent")
                {
                    sql.Append(" AND " + MapColumn(Alias.User.AgentID) + "=?");
                    Command.Parameters.Add("@" + Alias.User.AgentID, MapType(Alias.User.AgentID)).Value = new Guid(user["ID"]);
                }
                else if (Convert.ToBoolean(user["HasSelfLimitation"]))
                {
                    sql.Append(" AND (" + MapColumn(Alias.User.ID) + "=?");
                    sql.Append(" OR " + MapColumn(Alias.User.CreatorID) + "=?)");
                    Command.Parameters.Add("@" + Alias.User.ID, MapType(Alias.User.ID)).Value = new Guid(user["ID"]);
                    Command.Parameters.Add("@" + Alias.User.CreatorID, MapType(Alias.User.CreatorID)).Value = new Guid(user["ID"]);
                }
            }
			*/
            #endregion

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public DataTable List(int page, int pageSize, string condition, string sort)
        {
            DataTable result = new DataTable();
            result.Columns.Add(Alias.User.ID, MapCSType(Alias.User.ID));
            result.Columns.Add(Alias.User.InnerID, MapCSType(Alias.User.InnerID));
            result.Columns.Add(Alias.User.Name, MapCSType(Alias.User.Name));
			result.Columns.Add(Alias.User.Password, MapCSType(Alias.User.Password));
            result.Columns.Add(Alias.User.Mobile, MapCSType(Alias.User.Mobile));
			result.Columns.Add(Alias.User.IsMobileValidated, MapCSType(Alias.User.IsMobileValidated));
            result.Columns.Add(Alias.User.Nickname, MapCSType(Alias.User.Nickname));
            if (Lang != null)
                result.Columns.Add(Alias.User.RealName, MapCSType(Alias.User.RealName));
			result.Columns.Add(Alias.User.Sex, MapCSType(Alias.User.Sex));
            result.Columns.Add(Alias.User.Head, MapCSType(Alias.User.Head));
            result.Columns.Add(Alias.User.WxppOpenID, MapCSType(Alias.User.WxppOpenID));
			result.Columns.Add(Alias.User.WxmpOpenID, MapCSType(Alias.User.WxmpOpenID));
			result.Columns.Add(Alias.User.WxUnionID, MapCSType(Alias.User.WxUnionID));
            if (Lang != null)
            {
                result.Columns.Add(Alias.User.Profile, MapCSType(Alias.User.Profile));
            }
            result.Columns.Add(Alias.User.CreatorID, MapCSType(Alias.User.CreatorID));
            result.Columns.Add(Alias.User.CreatorName, MapCSType(Alias.User.CreatorName));
            result.Columns.Add(Alias.User.CreateTime, MapCSType(Alias.User.CreateTime));
            result.Columns.Add(Alias.User.LastLoginTime, MapCSType(Alias.User.LastLoginTime));
            result.Columns.Add(Alias.User.LastLoginIP, MapCSType(Alias.User.LastLoginIP));
            result.Columns.Add(Alias.User.LoginTimes, MapCSType(Alias.User.LoginTimes));
            result.Columns.Add(Alias.User.IsChecked, MapCSType(Alias.User.IsChecked));
            result.Columns.Add(Alias.User.IsLocked, MapCSType(Alias.User.IsLocked));
            result.Columns.Add(Alias.User.IsDeleted, MapCSType(Alias.User.IsDeleted));

            #region 条件
            Command.Parameters.Clear();
            StringBuilder where = new StringBuilder();
            if (!String.IsNullOrEmpty(condition))
            {
                if (Lang != null)
                    where.Append(" WHERE " + Alias.User.RowLang + " LIKE '%|" + Lang + "|%' AND ");
                else
                    where.Append(" WHERE ");
                where.Append("(" + condition + ")");
                ParseCondition(where, "");
            }
            else
            {
                if (Lang != null)
                    where.Append(" WHERE " + MapColumn(Alias.User.RowLang) + " LIKE '%|" + Lang + "|%'");
            }

            #region 权限
			/*
            if (CheckRight)
            {
                Dictionary<string, string> user = Manager.User;
                #region 代理
                if (user["Namespace"] == "AliCMS.UserModule.Agent")
                {
                    if (where.Length > 0)
                        where.Append(" AND ");

                    where.Append(MapColumn(Alias.User.AgentID) + "=?");
                    Command.Parameters.Add("@" + Alias.User.AgentID, MapType(Alias.User.AgentID)).Value = new Guid(user["ID"]);
                }
                #endregion
                #region HasSelfLimitation
                else if (Convert.ToBoolean(user["HasSelfLimitation"]))
                {
                    if (where.Length > 0)
                        where.Append(" AND ");

                    where.Append("(" + MapColumn(Alias.User.ID) + "=? OR " + MapColumn(Alias.User.CreatorID) + "=?)");
                    Command.Parameters.Add("@" + Alias.User.ID, MapType(Alias.User.ID)).Value = new Guid(user["ID"]);
                    Command.Parameters.Add("@" + Alias.User.CreatorID, MapType(Alias.User.CreatorID)).Value = new Guid(user["ID"]);
                }
                #endregion
            }
			*/
            #endregion
            #endregion

            #region 排序
            if (!String.IsNullOrEmpty(sort))
                sort = " ORDER BY " + ParseSort(sort, "");
            else
                sort = " ORDER BY " + MapColumn(Alias.User.InnerID) + " DESC";
            #endregion

            #region 构造SQL语句
            StringBuilder sql = new StringBuilder("SELECT");
            if (page > 0 && pageSize > 0)
                sql.Append(" TOP " + (page * pageSize));
            sql.Append(" " + MapColumn(Alias.User.ID));
            sql.Append("," + MapColumn(Alias.User.InnerID));
            sql.Append("," + MapColumn(Alias.User.Name));
            sql.Append("," + MapColumn(Alias.User.Password));
            sql.Append("," + MapColumn(Alias.User.Mobile));
			sql.Append("," + MapColumn(Alias.User.IsMobileValidated));
            sql.Append("," + MapColumn(Alias.User.Nickname));
            if (Lang != null)
                sql.Append("," + MapColumn(Alias.User.RealName, Lang));
			sql.Append("," + MapColumn(Alias.User.Sex));
            sql.Append("," + MapColumn(Alias.User.Head));
            sql.Append("," + MapColumn(Alias.User.WxppOpenID));
			sql.Append("," + MapColumn(Alias.User.WxmpOpenID));
			sql.Append("," + MapColumn(Alias.User.WxUnionID));
            if (Lang != null)
            {
                sql.Append("," + MapColumn(Alias.User.Profile, Lang));
            }
            sql.Append("," + MapColumn(Alias.User.CreatorID));
            sql.Append("," + MapColumn(Alias.User.CreatorName));
            sql.Append("," + MapColumn(Alias.User.CreateTime));
            sql.Append("," + MapColumn(Alias.User.LastLoginTime));
            sql.Append("," + MapColumn(Alias.User.LastLoginIP));
            sql.Append("," + MapColumn(Alias.User.LoginTimes));
            sql.Append("," + MapColumn(Alias.User.IsChecked));
            sql.Append("," + MapColumn(Alias.User.IsLocked));
            sql.Append("," + MapColumn(Alias.User.IsDeleted));
            sql.Append(" FROM " + MapTable(Alias.Objects.User));
            sql.Append(where + sort);
            Command.CommandText = sql.ToString();
            #endregion

            #region 执行SQL语句并获取记录
            int curIndex = 0, rowIndex = 0;
            int startIndex = (page - 1) * pageSize;
            DataReader = Command.ExecuteReader();
            while (DataReader.Read())
            {
                if (pageSize != -1)
                {
                    if (curIndex < startIndex)
                    {
                        curIndex++;
                        continue;
                    }

                    if (rowIndex == pageSize)
                        break;
                }

                DataRow row = result.NewRow();
                int i = 0;
                row[Alias.User.ID] = DataReader[i++];
                row[Alias.User.InnerID] = DataReader[i++];
                row[Alias.User.Name] = DataReader[i++];
                row[Alias.User.Password] = DataReader[i++];
                row[Alias.User.Mobile] = DataReader[i++];
				row[Alias.User.IsMobileValidated] = DataReader[i++];
                row[Alias.User.Nickname] = DataReader[i++];
                if (Lang != null)
                    row[Alias.User.RealName] = DataReader[i++];
				row[Alias.User.Sex] = DataReader[i++];
                row[Alias.User.Head] = DataReader[i++];
				row[Alias.User.WxppOpenID] = DataReader[i++];
				row[Alias.User.WxmpOpenID] = DataReader[i++];
				row[Alias.User.WxUnionID] = DataReader[i++];
                if (Lang != null)
                {
                    row[Alias.User.Profile] = DataReader[i++];
                }
                row[Alias.User.CreatorID] = DataReader[i++];
                row[Alias.User.CreatorName] = DataReader[i++];
                row[Alias.User.CreateTime] = DataReader[i++];
                row[Alias.User.LastLoginTime] = DataReader[i++];
                row[Alias.User.LastLoginIP] = DataReader[i++];
                row[Alias.User.LoginTimes] = DataReader[i++];
                row[Alias.User.IsChecked] = DataReader[i++];
                row[Alias.User.IsLocked] = DataReader[i++];
                row[Alias.User.IsDeleted] = DataReader[i++];
                result.Rows.Add(row);

                rowIndex++;
            }
            DataReader.Close();
            #endregion

            return result;
        }
        public int GetRecordCount(string condition)
        {
            #region 条件
            Command.Parameters.Clear();
            StringBuilder where = new StringBuilder();
            if (!String.IsNullOrEmpty(condition))
            {
                where.Append(" WHERE " + Alias.User.RowLang + " LIKE '%|" + Lang + "|%' AND ");
                where.Append("(" + condition + ")");
                ParseCondition(where, "");
            }
            else
            {
                where.Append(" WHERE " + MapColumn(Alias.User.RowLang) + " LIKE '%|" + Lang + "|%'");
            }

            #region 权限
			/*
            if (CheckRight)
            {
                Dictionary<string, string> user = Manager.User;
                #region 代理
                if (user["Namespace"] == "AliCMS.UserModule.Agent")
                {
                    if (where.Length > 0)
                        where.Append(" AND ");

                    where.Append(MapColumn(Alias.User.AgentID) + "=?");
                    Command.Parameters.Add("@" + Alias.User.AgentID, MapType(Alias.User.AgentID)).Value = new Guid(user["ID"]);
                }
                #endregion
                #region HasSelfLimitation
                else if (Convert.ToBoolean(user["HasSelfLimitation"]))
                {
                    if (where.Length > 0)
                        where.Append(" AND ");

                    where.Append("(" + MapColumn(Alias.User.ID) + "=? OR " + MapColumn(Alias.User.CreatorID) + "=?)");
                    Command.Parameters.Add("@" + Alias.User.ID, MapType(Alias.User.ID)).Value = new Guid(user["ID"]);
                    Command.Parameters.Add("@" + Alias.User.CreatorID, MapType(Alias.User.CreatorID)).Value = new Guid(user["ID"]);
                }
                #endregion
            }
			*/
            #endregion
            #endregion

            Command.CommandText = "SELECT COUNT(*) FROM " + MapTable(Alias.Objects.User) + where;
            return Convert.ToInt32(Command.ExecuteScalar());
        }

		public bool IsNameExist(string name)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.User.Name, MapType(Alias.User.Name)).Value = name;

			Command.CommandText = "SELECT TOP 1 " + MapColumn(Alias.User.InnerID) + " FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.Name) + "=?";
			object result = Command.ExecuteScalar();
			return result != null && !Convert.IsDBNull(result);
		}
		public bool IsMobileExist(string mobile)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.User.Mobile, MapType(Alias.User.Mobile)).Value = mobile;
			Command.Parameters.Add("@" + Alias.User.IsMobileValidated, MapType(Alias.User.IsMobileValidated)).Value = true;

			Command.CommandText = "SELECT TOP 1 " + MapColumn(Alias.User.InnerID) + " FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.Mobile) + "=? AND " + MapColumn(Alias.User.IsMobileValidated) + "=?";
			object result = Command.ExecuteScalar();
			return result != null && !Convert.IsDBNull(result);
		}
		public bool IsWxUnionIDExist(string wxUnionId)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.User.WxUnionID, MapType(Alias.User.WxUnionID)).Value = wxUnionId;

			Command.CommandText = "SELECT TOP 1 " + MapColumn(Alias.User.InnerID) + " FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.WxUnionID) + "=?";
			object result = Command.ExecuteScalar();
			return result != null && !Convert.IsDBNull(result);
		}
		public bool IsWxppOpenIDExist(string wxppOpenId)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.User.WxppOpenID, MapType(Alias.User.WxppOpenID)).Value = wxppOpenId;

			Command.CommandText = "SELECT TOP 1 " + MapColumn(Alias.User.InnerID) + " FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.WxppOpenID) + "=?";
			object result = Command.ExecuteScalar();
			return result != null && !Convert.IsDBNull(result);
		}
		public bool IsWxmpOpenIDExist(string wxmpOpenId)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.User.WxmpOpenID, MapType(Alias.User.WxmpOpenID)).Value = wxmpOpenId;

			Command.CommandText = "SELECT TOP 1 " + MapColumn(Alias.User.InnerID) + " FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.WxmpOpenID) + "=?";
			object result = Command.ExecuteScalar();
			return result != null && !Convert.IsDBNull(result);
		}

		public DataTable GetByWxUnionID(string wxUnionId)
		{
			DataTable result = new DataTable();
			result.Columns.Add(Alias.User.ID, MapCSType(Alias.User.ID));
			result.Columns.Add(Alias.User.InnerID, MapCSType(Alias.User.InnerID));
			result.Columns.Add(Alias.User.Name, MapCSType(Alias.User.Name));
			result.Columns.Add(Alias.User.Password, MapCSType(Alias.User.Password));
			result.Columns.Add(Alias.User.Mobile, MapCSType(Alias.User.Mobile));
			result.Columns.Add(Alias.User.IsMobileValidated, MapCSType(Alias.User.IsMobileValidated));
            result.Columns.Add(Alias.User.Nickname, MapCSType(Alias.User.Nickname));
            result.Columns.Add(Alias.User.RealName, MapCSType(Alias.User.RealName));
			result.Columns.Add(Alias.User.Sex, MapCSType(Alias.User.Sex));
			result.Columns.Add(Alias.User.Head, MapCSType(Alias.User.Head));
			result.Columns.Add(Alias.User.WxppOpenID, MapCSType(Alias.User.WxppOpenID));
			result.Columns.Add(Alias.User.WxmpOpenID, MapCSType(Alias.User.WxmpOpenID));
			result.Columns.Add(Alias.User.WxUnionID, MapCSType(Alias.User.WxUnionID));
			result.Columns.Add(Alias.User.Profile, MapCSType(Alias.User.Profile));
			result.Columns.Add(Alias.User.CreatorID, MapCSType(Alias.User.CreatorID));
			result.Columns.Add(Alias.User.CreatorName, MapCSType(Alias.User.CreatorName));
			result.Columns.Add(Alias.User.CreateTime, MapCSType(Alias.User.CreateTime));
			result.Columns.Add(Alias.User.LastLoginTime, MapCSType(Alias.User.LastLoginTime));
			result.Columns.Add(Alias.User.LastLoginIP, MapCSType(Alias.User.LastLoginIP));
			result.Columns.Add(Alias.User.LoginTimes, MapCSType(Alias.User.LoginTimes));
			result.Columns.Add(Alias.User.IsChecked, MapCSType(Alias.User.IsChecked));
			result.Columns.Add(Alias.User.IsLocked, MapCSType(Alias.User.IsLocked));
			result.Columns.Add(Alias.User.IsDeleted, MapCSType(Alias.User.IsDeleted));

			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.User.WxUnionID, MapType(Alias.User.WxUnionID)).Value = wxUnionId;

			#region 构造SQL语句
			StringBuilder sql = new StringBuilder("SELECT TOP 1");
			sql.Append(" " + MapColumn(Alias.User.ID));
			sql.Append("," + MapColumn(Alias.User.InnerID));
			sql.Append("," + MapColumn(Alias.User.Name));
			sql.Append("," + MapColumn(Alias.User.Password));
			sql.Append("," + MapColumn(Alias.User.Mobile));
			sql.Append("," + MapColumn(Alias.User.IsMobileValidated));
            sql.Append("," + MapColumn(Alias.User.Nickname));
            sql.Append("," + MapColumn(Alias.User.RealName, Lang));
			sql.Append("," + MapColumn(Alias.User.Sex));
			sql.Append("," + MapColumn(Alias.User.Head));
			sql.Append("," + MapColumn(Alias.User.WxppOpenID));
			sql.Append("," + MapColumn(Alias.User.WxmpOpenID));
			sql.Append("," + MapColumn(Alias.User.WxUnionID));
			sql.Append("," + MapColumn(Alias.User.Profile, Lang));
			sql.Append("," + MapColumn(Alias.User.CreatorID));
			sql.Append("," + MapColumn(Alias.User.CreatorName));
			sql.Append("," + MapColumn(Alias.User.CreateTime));
			sql.Append("," + MapColumn(Alias.User.LastLoginTime));
			sql.Append("," + MapColumn(Alias.User.LastLoginIP));
			sql.Append("," + MapColumn(Alias.User.LoginTimes));
			sql.Append("," + MapColumn(Alias.User.IsChecked));
			sql.Append("," + MapColumn(Alias.User.IsLocked));
			sql.Append("," + MapColumn(Alias.User.IsDeleted));
			sql.Append(" FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.WxUnionID) + "=?");
			Command.CommandText = sql.ToString();
			#endregion

			#region 执行SQL语句并获取记录
			DataReader = Command.ExecuteReader();
			if (DataReader.Read())
			{
				DataRow row = result.NewRow();
				int i = 0;
				row[Alias.User.ID] = DataReader[i++];
				row[Alias.User.InnerID] = DataReader[i++];
				row[Alias.User.Name] = DataReader[i++];
				row[Alias.User.Password] = DataReader[i++];
				row[Alias.User.Mobile] = DataReader[i++];
				row[Alias.User.IsMobileValidated] = DataReader[i++];
                row[Alias.User.Nickname] = DataReader[i++];
                row[Alias.User.RealName] = DataReader[i++];
				row[Alias.User.Sex] = DataReader[i++];
				row[Alias.User.Head] = DataReader[i++];
				row[Alias.User.WxppOpenID] = DataReader[i++];
				row[Alias.User.WxmpOpenID] = DataReader[i++];
				row[Alias.User.WxUnionID] = DataReader[i++];
				row[Alias.User.Profile] = DataReader[i++];
				row[Alias.User.CreatorID] = DataReader[i++];
				row[Alias.User.CreatorName] = DataReader[i++];
				row[Alias.User.CreateTime] = DataReader[i++];
				row[Alias.User.LastLoginTime] = DataReader[i++];
				row[Alias.User.LastLoginIP] = DataReader[i++];
				row[Alias.User.LoginTimes] = DataReader[i++];
				row[Alias.User.IsChecked] = DataReader[i++];
				row[Alias.User.IsLocked] = DataReader[i++];
				row[Alias.User.IsDeleted] = DataReader[i++];
				result.Rows.Add(row);
			}
			DataReader.Close();
			#endregion

			return result;
		}
        public DataTable GetByWxppOpenID(string wxppOpenId)
        {
            DataTable result = new DataTable();
            result.Columns.Add(Alias.User.ID, MapCSType(Alias.User.ID));
            result.Columns.Add(Alias.User.InnerID, MapCSType(Alias.User.InnerID));
            result.Columns.Add(Alias.User.Name, MapCSType(Alias.User.Name));
            result.Columns.Add(Alias.User.Password, MapCSType(Alias.User.Password));
            result.Columns.Add(Alias.User.Mobile, MapCSType(Alias.User.Mobile));
            result.Columns.Add(Alias.User.IsMobileValidated, MapCSType(Alias.User.IsMobileValidated));
            result.Columns.Add(Alias.User.Nickname, MapCSType(Alias.User.Nickname));
            result.Columns.Add(Alias.User.RealName, MapCSType(Alias.User.RealName));
            result.Columns.Add(Alias.User.Sex, MapCSType(Alias.User.Sex));
            result.Columns.Add(Alias.User.Head, MapCSType(Alias.User.Head));
            result.Columns.Add(Alias.User.WxppOpenID, MapCSType(Alias.User.WxppOpenID));
            result.Columns.Add(Alias.User.WxmpOpenID, MapCSType(Alias.User.WxmpOpenID));
            result.Columns.Add(Alias.User.WxUnionID, MapCSType(Alias.User.WxUnionID));
            result.Columns.Add(Alias.User.Profile, MapCSType(Alias.User.Profile));
            result.Columns.Add(Alias.User.CreatorID, MapCSType(Alias.User.CreatorID));
            result.Columns.Add(Alias.User.CreatorName, MapCSType(Alias.User.CreatorName));
            result.Columns.Add(Alias.User.CreateTime, MapCSType(Alias.User.CreateTime));
            result.Columns.Add(Alias.User.LastLoginTime, MapCSType(Alias.User.LastLoginTime));
            result.Columns.Add(Alias.User.LastLoginIP, MapCSType(Alias.User.LastLoginIP));
            result.Columns.Add(Alias.User.LoginTimes, MapCSType(Alias.User.LoginTimes));
            result.Columns.Add(Alias.User.IsChecked, MapCSType(Alias.User.IsChecked));
            result.Columns.Add(Alias.User.IsLocked, MapCSType(Alias.User.IsLocked));
            result.Columns.Add(Alias.User.IsDeleted, MapCSType(Alias.User.IsDeleted));

            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.User.WxppOpenID, MapType(Alias.User.WxppOpenID)).Value = wxppOpenId;

            #region 构造SQL语句
            StringBuilder sql = new StringBuilder("SELECT TOP 1");
            sql.Append(" " + MapColumn(Alias.User.ID));
            sql.Append("," + MapColumn(Alias.User.InnerID));
            sql.Append("," + MapColumn(Alias.User.Name));
            sql.Append("," + MapColumn(Alias.User.Password));
            sql.Append("," + MapColumn(Alias.User.Mobile));
            sql.Append("," + MapColumn(Alias.User.IsMobileValidated));
            sql.Append("," + MapColumn(Alias.User.Nickname));
            sql.Append("," + MapColumn(Alias.User.RealName, Lang));
            sql.Append("," + MapColumn(Alias.User.Sex));
            sql.Append("," + MapColumn(Alias.User.Head));
            sql.Append("," + MapColumn(Alias.User.WxppOpenID));
            sql.Append("," + MapColumn(Alias.User.WxmpOpenID));
            sql.Append("," + MapColumn(Alias.User.WxUnionID));
            sql.Append("," + MapColumn(Alias.User.Profile, Lang));
            sql.Append("," + MapColumn(Alias.User.CreatorID));
            sql.Append("," + MapColumn(Alias.User.CreatorName));
            sql.Append("," + MapColumn(Alias.User.CreateTime));
            sql.Append("," + MapColumn(Alias.User.LastLoginTime));
            sql.Append("," + MapColumn(Alias.User.LastLoginIP));
            sql.Append("," + MapColumn(Alias.User.LoginTimes));
            sql.Append("," + MapColumn(Alias.User.IsChecked));
            sql.Append("," + MapColumn(Alias.User.IsLocked));
            sql.Append("," + MapColumn(Alias.User.IsDeleted));
            sql.Append(" FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.WxppOpenID) + "=?");
            Command.CommandText = sql.ToString();
            #endregion

            #region 执行SQL语句并获取记录
            DataReader = Command.ExecuteReader();
            if (DataReader.Read())
            {
                DataRow row = result.NewRow();
                int i = 0;
                row[Alias.User.ID] = DataReader[i++];
                row[Alias.User.InnerID] = DataReader[i++];
                row[Alias.User.Name] = DataReader[i++];
                row[Alias.User.Password] = DataReader[i++];
                row[Alias.User.Mobile] = DataReader[i++];
                row[Alias.User.IsMobileValidated] = DataReader[i++];
                row[Alias.User.Nickname] = DataReader[i++];
                row[Alias.User.RealName] = DataReader[i++];
                row[Alias.User.Sex] = DataReader[i++];
                row[Alias.User.Head] = DataReader[i++];
                row[Alias.User.WxppOpenID] = DataReader[i++];
                row[Alias.User.WxmpOpenID] = DataReader[i++];
                row[Alias.User.WxUnionID] = DataReader[i++];
                row[Alias.User.Profile] = DataReader[i++];
                row[Alias.User.CreatorID] = DataReader[i++];
                row[Alias.User.CreatorName] = DataReader[i++];
                row[Alias.User.CreateTime] = DataReader[i++];
                row[Alias.User.LastLoginTime] = DataReader[i++];
                row[Alias.User.LastLoginIP] = DataReader[i++];
                row[Alias.User.LoginTimes] = DataReader[i++];
                row[Alias.User.IsChecked] = DataReader[i++];
                row[Alias.User.IsLocked] = DataReader[i++];
                row[Alias.User.IsDeleted] = DataReader[i++];
                result.Rows.Add(row);
            }
            DataReader.Close();
            #endregion

            return result;
        }
        public DataTable GetByMobile(string mobile)
		{
			DataTable result = new DataTable();
			result.Columns.Add(Alias.User.ID, MapCSType(Alias.User.ID));
			result.Columns.Add(Alias.User.InnerID, MapCSType(Alias.User.InnerID));
			result.Columns.Add(Alias.User.Name, MapCSType(Alias.User.Name));
			result.Columns.Add(Alias.User.Password, MapCSType(Alias.User.Password));
			result.Columns.Add(Alias.User.Mobile, MapCSType(Alias.User.Mobile));
			result.Columns.Add(Alias.User.IsMobileValidated, MapCSType(Alias.User.IsMobileValidated));
            result.Columns.Add(Alias.User.Nickname, MapCSType(Alias.User.Nickname));
            result.Columns.Add(Alias.User.RealName, MapCSType(Alias.User.RealName));
			result.Columns.Add(Alias.User.Sex, MapCSType(Alias.User.Sex));
			result.Columns.Add(Alias.User.Head, MapCSType(Alias.User.Head));
			result.Columns.Add(Alias.User.WxppOpenID, MapCSType(Alias.User.WxppOpenID));
			result.Columns.Add(Alias.User.WxmpOpenID, MapCSType(Alias.User.WxmpOpenID));
			result.Columns.Add(Alias.User.WxUnionID, MapCSType(Alias.User.WxUnionID));
			result.Columns.Add(Alias.User.Profile, MapCSType(Alias.User.Profile));
			result.Columns.Add(Alias.User.CreatorID, MapCSType(Alias.User.CreatorID));
			result.Columns.Add(Alias.User.CreatorName, MapCSType(Alias.User.CreatorName));
			result.Columns.Add(Alias.User.CreateTime, MapCSType(Alias.User.CreateTime));
			result.Columns.Add(Alias.User.LastLoginTime, MapCSType(Alias.User.LastLoginTime));
			result.Columns.Add(Alias.User.LastLoginIP, MapCSType(Alias.User.LastLoginIP));
			result.Columns.Add(Alias.User.LoginTimes, MapCSType(Alias.User.LoginTimes));
			result.Columns.Add(Alias.User.IsChecked, MapCSType(Alias.User.IsChecked));
			result.Columns.Add(Alias.User.IsLocked, MapCSType(Alias.User.IsLocked));
			result.Columns.Add(Alias.User.IsDeleted, MapCSType(Alias.User.IsDeleted));

			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.User.Mobile, MapType(Alias.User.Mobile)).Value = mobile;
			Command.Parameters.Add("@" + Alias.User.IsMobileValidated, MapType(Alias.User.IsMobileValidated)).Value = true;

			#region 构造SQL语句
			StringBuilder sql = new StringBuilder("SELECT TOP 1");
			sql.Append(" " + MapColumn(Alias.User.ID));
			sql.Append("," + MapColumn(Alias.User.InnerID));
			sql.Append("," + MapColumn(Alias.User.Name));
			sql.Append("," + MapColumn(Alias.User.Password));
			sql.Append("," + MapColumn(Alias.User.Mobile));
			sql.Append("," + MapColumn(Alias.User.IsMobileValidated));
            sql.Append("," + MapColumn(Alias.User.Nickname));
            sql.Append("," + MapColumn(Alias.User.RealName, Lang));
			sql.Append("," + MapColumn(Alias.User.Sex));
			sql.Append("," + MapColumn(Alias.User.Head));
			sql.Append("," + MapColumn(Alias.User.WxppOpenID));
			sql.Append("," + MapColumn(Alias.User.WxmpOpenID));
			sql.Append("," + MapColumn(Alias.User.WxUnionID));
			sql.Append("," + MapColumn(Alias.User.Profile, Lang));
			sql.Append("," + MapColumn(Alias.User.CreatorID));
			sql.Append("," + MapColumn(Alias.User.CreatorName));
			sql.Append("," + MapColumn(Alias.User.CreateTime));
			sql.Append("," + MapColumn(Alias.User.LastLoginTime));
			sql.Append("," + MapColumn(Alias.User.LastLoginIP));
			sql.Append("," + MapColumn(Alias.User.LoginTimes));
			sql.Append("," + MapColumn(Alias.User.IsChecked));
			sql.Append("," + MapColumn(Alias.User.IsLocked));
			sql.Append("," + MapColumn(Alias.User.IsDeleted));
			sql.Append(" FROM " + MapTable(Alias.Objects.User) + " WHERE " + MapColumn(Alias.User.Mobile) + "=? and " + MapColumn(Alias.User.IsMobileValidated) + "=?");
			Command.CommandText = sql.ToString();
			#endregion

			#region 执行SQL语句并获取记录
			DataReader = Command.ExecuteReader();
			if (DataReader.Read())
			{
				DataRow row = result.NewRow();
				int i = 0;
				row[Alias.User.ID] = DataReader[i++];
				row[Alias.User.InnerID] = DataReader[i++];
				row[Alias.User.Name] = DataReader[i++];
				row[Alias.User.Password] = DataReader[i++];
				row[Alias.User.Mobile] = DataReader[i++];
				row[Alias.User.IsMobileValidated] = DataReader[i++];
                row[Alias.User.Nickname] = DataReader[i++];
                row[Alias.User.RealName] = DataReader[i++];
				row[Alias.User.Sex] = DataReader[i++];
				row[Alias.User.Head] = DataReader[i++];
				row[Alias.User.WxppOpenID] = DataReader[i++];
				row[Alias.User.WxmpOpenID] = DataReader[i++];
				row[Alias.User.WxUnionID] = DataReader[i++];
				row[Alias.User.Profile] = DataReader[i++];
				row[Alias.User.CreatorID] = DataReader[i++];
				row[Alias.User.CreatorName] = DataReader[i++];
				row[Alias.User.CreateTime] = DataReader[i++];
				row[Alias.User.LastLoginTime] = DataReader[i++];
				row[Alias.User.LastLoginIP] = DataReader[i++];
				row[Alias.User.LoginTimes] = DataReader[i++];
				row[Alias.User.IsChecked] = DataReader[i++];
				row[Alias.User.IsLocked] = DataReader[i++];
				row[Alias.User.IsDeleted] = DataReader[i++];
				result.Rows.Add(row);
			}
			DataReader.Close();
			#endregion

			return result;
		}

        public Dictionary<string, object> Login(string name, string password)
        {
            Dictionary<string, object> result = new Dictionary<string, object>();

            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.User.Name, MapType(Alias.User.Name)).Value = name;
			Command.Parameters.Add("@" + Alias.User.Mobile, MapType(Alias.User.Mobile)).Value = name;
			Command.Parameters.Add("@" + Alias.User.IsMobileValidated, MapType(Alias.User.IsMobileValidated)).Value = true;
			Command.Parameters.Add("@" + Alias.User.Password, MapType(Alias.User.Password)).Value = password;

            StringBuilder sql = new StringBuilder("SELECT TOP 1");
            sql.Append(" " + MapColumn(Alias.User.ID));
            sql.Append("," + MapColumn(Alias.User.InnerID));
            sql.Append("," + MapColumn(Alias.User.Name));
            sql.Append("," + MapColumn(Alias.User.Mobile));
            sql.Append("," + MapColumn(Alias.User.IsMobileValidated));
			sql.Append("," + MapColumn(Alias.User.WxppOpenID));
			sql.Append("," + MapColumn(Alias.User.WxmpOpenID));
			sql.Append("," + MapColumn(Alias.User.WxUnionID));
			sql.Append("," + MapColumn(Alias.User.Head));
            sql.Append("," + MapColumn(Alias.User.Profile, Lang));
            sql.Append("," + MapColumn(Alias.User.CreatorID));
            sql.Append("," + MapColumn(Alias.User.CreatorName));
            sql.Append("," + MapColumn(Alias.User.CreateTime));
            sql.Append("," + MapColumn(Alias.User.LastLoginTime));
            sql.Append("," + MapColumn(Alias.User.LastLoginIP));
            sql.Append("," + MapColumn(Alias.User.LoginTimes));
            sql.Append("," + MapColumn(Alias.User.IsChecked));
            sql.Append("," + MapColumn(Alias.User.IsLocked));
            sql.Append("," + MapColumn(Alias.User.IsDeleted));
			sql.Append(" FROM " + MapTable(Alias.Objects.User) + " WHERE (" + MapColumn(Alias.User.Name) + "=? OR (" + MapColumn(Alias.User.Mobile) + "=? AND " + MapColumn(Alias.User.IsMobileValidated) + "=?)) AND " + MapColumn(Alias.User.Password) + "=?");

            Command.CommandText = sql.ToString();
            DataReader = Command.ExecuteReader();
            if (!DataReader.HasRows)
            {
                DataReader.Close();
                return null;
            }

            DataReader.Read();
            int i = 0;
            result[Alias.User.ID] = DataReader[i++];
            result[Alias.User.InnerID] = DataReader[i++];
            result[Alias.User.Name] = DataReader[i++];
            result[Alias.User.Mobile] = DataReader[i++];
            result[Alias.User.IsMobileValidated] = DataReader[i++];
			result[Alias.User.WxppOpenID] = DataReader[i++];
			result[Alias.User.WxmpOpenID] = DataReader[i++];
			result[Alias.User.WxUnionID] = DataReader[i++];
			result[Alias.User.Head] = DataReader[i++];
            result[Alias.User.Profile] = DataReader[i++];
            result[Alias.User.CreatorID] = DataReader[i++];
            result[Alias.User.CreatorName] = DataReader[i++];
            result[Alias.User.CreateTime] = DataReader[i++];
            result[Alias.User.LastLoginTime] = DataReader[i++];
            result[Alias.User.LastLoginIP] = DataReader[i++];
            result[Alias.User.LoginTimes] = DataReader[i++];
            result[Alias.User.IsChecked] = DataReader[i++];
            result[Alias.User.IsLocked] = DataReader[i++];
            result[Alias.User.IsDeleted] = DataReader[i++];
            DataReader.Close();

            return result;
        }
    }
}
